<?php

class m_dosen extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }

    // get total data dosen
    function get_total_data_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                WHERE c.role_id = 4 AND a.nama_lengkap LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data dosen
    function get_data_dosen($params) {
        $sql = "SELECT a.* FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                WHERE c.role_id = 4 AND a.nama_lengkap LIKE ? 
                ORDER BY a.nama_lengkap ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail data
    function get_detail_data_dosen($params) {
        $sql = "SELECT a.user_name, a.user_pass, 
                a.user_key, b.* 
                FROM com_user a 
                JOIN users b ON a.user_id = b.user_id 
                WHERE b.user_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // insert data dosen
    function insert_data_dosen($params) {
        $sql = "INSERT INTO users (user_id, nama_lengkap, dosen_gelar, dosen_nip, dosen_status, mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data akun
    function insert_akun_dosen($params) {
        $sql = "INSERT INTO com_user (user_name, user_pass, user_key, mdb, mdd) 
            VALUES (?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert role dosen
    function insert_role_dosen($params) {
        $sql = "INSERT INTO com_role_user (role_id, user_id) VALUES(?, ?)";
        return $this->db->query($sql, $params);
    }

    // update data akun
    function update_akun_dosen($params) {
        $sql = "UPDATE com_user SET user_name = ?, user_pass = ?, user_key = ?, mdb = ?, mdd = NOW() 
            WHERE user_id = ?";
        return $this->db->query($sql, $params);
    }

    // update data dosen
    function update_data_dosen($params) {
        $sql = "UPDATE users SET nama_lengkap = ?, dosen_gelar = ?, dosen_nip = ?, dosen_status = ?, mdb = ?, mdd = NOW() 
            WHERE user_id = ?";
        return $this->db->query($sql, $params);
    }

    // hapus akun dosen
    function delete_akun_dosen($params) {
        $sql = "DELETE FROM com_user WHERE user_id = ?";
        return $this->db->query($sql, $params);
    }

    /*
     *  PENGATURAN DOSEN AJAR
     */

    // get data group dosenajar
    function get_data_group_dosenajar() {
        $sql = "SELECT * FROM setting_dosen_ajar  
            ORDER BY tahun DESC, semester DESC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail setting dosen ajar
    function get_detail_setting_dosenajar($params) {
        $sql = "SELECT * FROM setting_dosen_ajar  
            WHERE set_dosenajar_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total data set dosenajar detail
    function get_total_data_set_dosenajar_detail($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
                FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.set_dosenajar_kd = ? AND c.nama_lengkap LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data set dosenajar detail
    function get_data_set_dosenajar_detail($params) {
        $sql = "SELECT a.*, b.dosen_kd, b.makul_kd, 
                c.nama_lengkap, c.dosen_gelar, d.makul_nm, 
                d.makul_sks  
                FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.set_dosenajar_kd = ? AND c.nama_lengkap LIKE ?  
                ORDER BY c.nama_lengkap ASC, d.makul_nm ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data dosen aktif
    function get_data_dosen_aktif() {
        $sql = "SELECT b.* FROM com_user a 
                JOIN users b ON a.user_id = b.user_id 
                JOIN com_role_user c ON a.user_id = c.user_id 
                WHERE b.dosen_status = 'aktif' AND c.role_id = 4 
                ORDER BY b.nama_lengkap ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data mata kuliah aktif
    function get_data_makul_aktif($params) {
        $sql = "SELECT c.*, b.set_jur_1, b.set_jur_2, b.set_jur_3, b.set_jur_4 
                FROM setting_makul a 
                JOIN setting_makul_detail b ON a.set_makul_kd = b.set_makul_kd 
                JOIN mata_kuliah c ON b.makul_kd = c.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // cek setting dosenajar
    function is_setting_dosenajar_exist($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM setting_dosen_ajar  
            WHERE tahun = ? AND semester = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 1;
        }
    }

    // cek data dosen ajar
    function is_data_dosenajar_exist($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM setting_dosen_ajar_detail 
            WHERE set_dosenajar_kd = ? AND dosen_kd = ? 
            AND makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            if ($result['total'] > 0) {
                return TRUE;
            } else {
                return FALSE;
            }
        } else {
            return FALSE;
        }
    }

    // insert setting dosenajar
    function insert_setting_dosenajar($params) {
        $sql = "INSERT INTO setting_dosen_ajar (semester, tahun, mdb, mdd) 
            VALUES(?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert data dosen ajar
    function insert_data_dosenajar($params) {
        $sql = "INSERT INTO setting_dosen_ajar_detail (set_dosenajar_kd, dosen_kd, makul_kd, mdb, mdd) 
            VALUES(?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // hapus data dosen ajar
    function delete_data_dosenajar($params) {
        $sql = "DELETE FROM setting_dosen_ajar_detail 
            WHERE set_dosenajar_kd = ? AND dosen_kd = ? 
            AND makul_kd = ?";
        return $this->db->query($sql, $params);
    }

}